package goodroommate.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import goodroommate.dal.DatabaseSession;
import goodroommate.dao.IMessageDao;
import goodroommate.domain.Message;
import goodroommate.exception.DatabaseException;

public class MessageDao implements IMessageDao {

	@Override
	public boolean addMessage(Message message, DatabaseSession dbs) throws DatabaseException {
		String sql="INSERT messages (Source,Destination,Handle) VALUES(?,?,?)";
		try{
			dbs.executeUpdate(sql,message.getSource(),message.getDestination(),message.getHandle());
			return true;
		}catch(DatabaseException e){
			throw new DatabaseException(e);
		}
	}

	@Override
	public boolean updateMessageHandle(Message message, DatabaseSession dbs) throws DatabaseException {

		String sql="UPDATE messages SET ";
		if(message.getHandle()==0)
			sql+="Handle=1 ";
		if(message.getHandle()==1)
			sql+="Handle=0 ";
		sql+="WHERE ID=?";
		try{
			dbs.executeUpdate(sql,message.getMessageid());
			return true;
		}catch(DatabaseException e){
			throw new DatabaseException(e);
		}
	}

	@Override
	public ArrayList<Message> getMessages(Long source, long destination, Integer handle, DatabaseSession dbs)
			throws DatabaseException {
		String sql="SELECT messages.*,First.Username AS Sourcename,Second.Username AS Destinationname FROM messages "
				+"LEFT JOIN users First "
				+ "ON messages.Source=First.ID "
				+ "LEFT JOIN users Second "
				+ "ON messages.Destination=Second.ID WHERE TRUE ";
		ArrayList<Object>params=new ArrayList<Object>();
		if(source!=0){
			sql += "AND Source=? ";
			params.add(source);
		}
		if(destination!=0){
			sql += "AND Destination=? ";
			params.add(destination);
		}
		if (handle != 10000) {
			sql += "AND Handle=?";
			params.add(handle);
		}
	
		try{
			ArrayList<Message>rst=new ArrayList<Message>();
			ResultSet rs = dbs.executeQuery(sql, params);
			while(rs.next()){
				Message message=new Message();
				message.setMessageid(rs.getInt("ID"));
				message.setSource(rs.getInt("Source"));
				message.setSourcename(rs.getString("Sourcename"));
				message.setDestination(rs.getInt("Destination"));
				message.setDestinationname(rs.getString("Destinationname"));
				message.setHandle(rs.getInt("Handle"));
				message.setTime(rs.getTimestamp("Time"));
				rst.add(message);
			}
			return rst;
		}catch(SQLException e){
			throw new DatabaseException(e);
		}
	}
	
	@Override
	public Message getMessageById(long messageid,DatabaseSession dbs)throws DatabaseException{
		
		String sql="SELECT messages.*,First.Username AS Sourcename,Second.Username AS Destinationname FROM messages "
				+"LEFT JOIN users First "
				+ "ON messages.Source=First.ID "
				+ "LEFT JOIN users Second "
				+ "ON messages.Destination=Second.ID "
				+ "WHERE messages.ID=?";
		try{
			ResultSet rs=dbs.executeQuery(sql, messageid);
			Message message=new Message();
			while(rs.next()){
				
				message.setMessageid(rs.getInt("ID"));
				message.setSource(rs.getInt("Source"));
				message.setSourcename(rs.getString("Sourcename"));
				message.setDestination(rs.getInt("Destination"));
				message.setDestinationname(rs.getString("Destinationname"));
				message.setHandle(rs.getInt("Handle"));
				message.setTime(rs.getTimestamp("Time"));
			}
			return message;
		}catch(SQLException e){
			throw new DatabaseException(e);
		}
	}

}
